package matchthreegame.dataBase;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;

import matchthreegame.extensions.MatchThreeGameZoneExtension;
import matchthreegame.utils.GameOverInfo;

import com.smartfoxserver.v2.db.IDBManager;
import com.smartfoxserver.v2.entities.User;
import com.smartfoxserver.v2.entities.variables.SFSUserVariable;
import com.smartfoxserver.v2.entities.variables.UserVariable;

public class DBManagerHelper  {
	
	public static final String PLAYER_NAME		 = "name";
	public static final String PLAYER_PASSWORD	 = "pword";
	public static final String PLAYER_WINS		 = "wins";
	public static final String PLAYER_LOSSES	 = "losses";
	public static final String PLAYER_RANK		 = "rank";
	
	public static final int LOSE_MONEY_PRIZE = 10;
	public static final int WIN_MONEY_PRIZE  = 30;
	public static final int WIN_RANK         = 10;
	public static final int LOSE_RANK        = -5;
	
	public static final String NAME           = "name";
	public static final String RANK           = "rank";
	public static final String HERO_TYPE      = "heroType";
	public static final String HERO_PRODUCES  = "heroProduces";
	public static final String HERO_ATTACK    = "heroAttack";
	public static final String WIN_PERCENTAGE = "WP";
	public static final String PLAYED_GAMES   = "PG";
	public static final String BOT_LEVEL      = "botLevel";
	
	public static final int UPGRADE_PRODUCES_TYPE = 1;
	public static final int UPGRADE_ATTACK_TYPE   = 2;
	
	private IDBManager dbManager;
	private MatchThreeGameZoneExtension zoneExtension;


	public DBManagerHelper(IDBManager dbManager, MatchThreeGameZoneExtension extension) 
	{
		this.dbManager = dbManager;
		this.zoneExtension = extension;
	}
	
	
	// SELECT fields from 'user' table, for particular userName and return them as UserVariables
	public List<UserVariable> getVariablesForUser(String userName) 
	{
		
		Connection connection = null;
		List<UserVariable> variables = null;
		PreparedStatement statement = null;
		try
		{
			// Grab a connection from the DBManager connection pool
			connection = dbManager.getConnection();

			// Build a prepared statement
			statement = connection.prepareStatement("SELECT * FROM user WHERE name=?");
			statement.setString(1, userName);

			// Execute query
			ResultSet result = statement.executeQuery();

			// Verify that one record was found
			if ( !result.first() )
			{
				zoneExtension.trace("There is no record for username: " + userName);
			}
			
			UserVariable name = new SFSUserVariable("name", result.getString("name"));
//			UserVariable pword = new SFSUserVariable("pword", result.getString("pword"));
			UserVariable wins = new SFSUserVariable("wins", result.getInt("wins"));
			UserVariable losses = new SFSUserVariable("losses", result.getInt("losses"));
			UserVariable rank = new SFSUserVariable("rank", result.getInt("rank"));
//			UserVariable money = new SFSUserVariable("money", result.getInt("money"));
			UserVariable heroType = new SFSUserVariable("heroType", result.getInt("heroType"));
			UserVariable heroProduces = new SFSUserVariable(HERO_PRODUCES, result.getInt(HERO_PRODUCES));
			UserVariable heroAttack = new SFSUserVariable(HERO_ATTACK, result.getInt(HERO_ATTACK));
			UserVariable botLevel = new SFSUserVariable(BOT_LEVEL, result.getInt(BOT_LEVEL));
			
			int isBot = result.getInt("isBot");
			
			// Put variables for bot
			if ( isBot != 0 ) {
				
				variables = Arrays.asList(wins, rank, losses, heroType, heroProduces, heroAttack, botLevel);
				
			} else {
				
				// Put the variables for Real player
				variables = Arrays.asList(name, wins, rank, losses);
				
			}
			
		}
		
		// User name was not found
		catch (SQLException e)
		{
			zoneExtension.trace("SQL Error: " + e.getMessage());
			return variables;
		}

		finally
		{
			// Return connection to the DBManager connection pool
	
			try {
				if ( statement != null ) {
					statement.close();
				}
				if ( connection != null ) {
					connection.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}			
		}
		
		return variables;
		
	}
	
	
	// INSERT new User to 'user' table
	public void insertUser(String userName, String pword, int gameId) 
	{
		
		Connection connection = null;
		PreparedStatement statement = null;
		try
		{
			// Grab a connection from the DBManager connection pool
			connection = dbManager.getConnection();
			
			// Prepare the statement
			statement = connection.prepareStatement("INSERT INTO `user` "
						+ "(`name`, `pword`,`gameId`, `wins`, `losses`, `rank`, `money`, `isBot`, `botLevel`, `heroType`) " 
						+ " VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ? )");
			
			statement.setObject(1, userName);
			statement.setObject(2, pword);
			statement.setObject(3, gameId);
			statement.setObject(4, 0);    // wins
			statement.setObject(5, 0);    // losses
			statement.setObject(6, 1000); // starting Rank
			statement.setObject(7, 0);    // money
			statement.setObject(8, 0);    // isBot
			statement.setObject(9, 1);    // botLevel
			statement.setObject(10, 1);   // heroType
			
			// Execute Insert
			statement.executeUpdate();
					
		}
		
		// User name was already Registered
		catch (SQLException e)
		{
			zoneExtension.trace("SQL Error: " + e.getMessage());
		}

		finally
		{
			// Return connection to the DBManager connection pool
			try {
				if ( statement != null ) {
					statement.close();
				}
				if ( connection != null ) {
					connection.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}			
		}
		
	}
	
	
	// UPDATE field in 'user' table for particular userName
	public void updateFieldsForUserWithValues(String userName, boolean hasWon) 
	{
		
		Connection connection = null;
		PreparedStatement statement = null;
		try
		{
			// Grab a connection from the DBManager connection pool
			connection = dbManager.getConnection();
			
			// Increase the field value by 1 for particular userName
			String sqlString;
			
			if ( hasWon == true ) {
				sqlString = "UPDATE `user` SET wins = wins + 1 , money = money + " + WIN_MONEY_PRIZE + 
							", rank = rank + " + WIN_RANK + " WHERE name =?";
			} else {
				sqlString = "UPDATE `user` SET losses = losses + 1 , money = money + " + LOSE_MONEY_PRIZE + 
							", rank = rank + " + LOSE_RANK + " WHERE name =?";
			}
			
			// Prepare the statement
			statement = connection.prepareStatement(sqlString);
			statement.setString(1, userName);
			
			zoneExtension.trace("statement.toString(): " + statement.toString());
			
			// Execute UPDATE
			statement.executeUpdate();
			
		}

		// There is some SQL Error
		catch (SQLException e)
		{
			zoneExtension.trace("SQL Error: " + e.getMessage());
		}

		finally
		{
			// Return connection to the DBManager connection pool
			try {
				if ( statement != null ) {
					statement.close();
				}
				if ( connection != null ) {
					connection.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}			
		}

	}
	
	
	public void updateMoneyForUser(int money, String userName) {
		
		Connection connection = null;
		PreparedStatement statement = null;
		try
		{
			// Grab a connection from the DBManager connection pool
			connection = dbManager.getConnection();
			
			// Increase the field value by 1 for particular userName
			String sqlString;
			
			sqlString = "UPDATE `user` SET money = money + ? WHERE name =?";
			
			// Prepare the statement
			statement = connection.prepareStatement(sqlString);
			statement.setInt(1, money);
			statement.setString(2, userName);
			
			zoneExtension.trace("updateMoneyForUser statement: " + statement.toString());
			
			// Execute UPDATE
			statement.executeUpdate();
			
		}

		// There is some SQL Error
		catch (SQLException e)
		{
			zoneExtension.trace("SQL Error: " + e.getMessage());
		}

		finally
		{
			// Return connection to the DBManager connection pool
			try {
				if ( statement != null ) {
					statement.close();
				}
				if ( connection != null ) {
					connection.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}			
		}
		
	}
	
	
	// SELECT name field from 'user' table, for particular username 
	//			and return Array with names containing this username
	
	public ArrayList<String> getListOfUsersForName(String username) 
	{

		Connection connection = null;
		ArrayList<String> userNames = null;
		PreparedStatement statement = null;
		
		try
		{
			// Grab a connection from the DBManager connection pool
			connection = dbManager.getConnection();

			// Build a prepared statement
			statement = connection.prepareStatement("SELECT name FROM user WHERE name LIKE ? LIMIT 50");
			statement.setString(1, "%" + username + "%");
			
			zoneExtension.trace("SQL statement: " + statement.toString());

			// Execute query
			ResultSet result = statement.executeQuery();

			// Verify that one record was found
			if ( !result.first() ) {
				
				zoneExtension.trace("There is no record for username: " + username);

			} else {
				
				// Get number of found usernames
				ResultSetMetaData metadata = result.getMetaData();
				int numberOfColumns = metadata.getColumnCount();
				
				zoneExtension.trace("firstResult: " + result.getString(1));

				// Put all found usernames in array
				userNames = new ArrayList<String>();
				
				// move beforeFirst or the first result will be missing
				if ( numberOfColumns > 0 ) {
					result.beforeFirst();
				}
				
				while ( result.next() ) {
					userNames.add(result.getString(1));
				}
				
			}
			
		}
		
		catch (SQLException e)
		{
			zoneExtension.trace("SQL Error: " + e.getMessage());
			return userNames;
		}

		finally
		{
			// Return connection to the DBManager connection pool
			try {
				if ( statement != null ) {
					statement.close();
				}
				if ( connection != null ) {
					connection.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}			
		}
		
		return userNames;
	}
	
	
	public ArrayList<HashMap<String, String>> getWorldRankingList(User sender)
	{
		
		Connection connection = null;
		ArrayList<HashMap<String, String>> users = new ArrayList<HashMap<String, String>>();
		PreparedStatement statement = null;
		
		try
		{
			// Grab a connection from the DBManager connection pool
			connection = dbManager.getConnection();

			// Build a prepared statement
			
			// SELECT name, rank FROM user ORDER BY rank DESC LIMIT 25
			statement = connection.prepareStatement("SELECT * FROM ( "
					+ " SELECT user.name, user.rank, user.wins, user.losses FROM user "
					+ " WHERE user.name = ? ) DUMMY_ALIAS1 " // Select sender statistics
					+ " UNION ALL " // Combine the two selects
					+ " SELECT * FROM ( "
					+ " SELECT user.name, user.rank, user.wins, user.losses FROM user "
					+ " ORDER BY user.rank DESC LIMIT 25 ) DUMMY_ALIAS2"); // Select top25 players statistics
			
			statement.setString(1, sender.getName());
			
			// Execute query
			ResultSet result = statement.executeQuery();

			// Verify that one record was found
			if ( !result.first() ) {
				
				zoneExtension.trace("There is a problem in getWorldRankingList()");
				
			} else {				
				
				// Get number of found usernames
				ResultSetMetaData metadata = result.getMetaData();
				int numberOfColumns = metadata.getColumnCount();
				
//				zoneExtension.trace("firstResult 1 : " + result.getString(1)); // name
//				zoneExtension.trace("firstResult 2 : " + result.getString(2)); // rank
//				zoneExtension.trace("firstResult 3 : " + result.getString(3)); // wins
//				zoneExtension.trace("firstResult 4 : " + result.getString(4)); // losses
				
				// move beforeFirst or the first result will be missing
				if ( numberOfColumns > 0 ) {
					result.beforeFirst();
				}
				
				while ( result.next() ) {
					
					// Calculate playedGames and winPercentage
					int wins = result.getInt(3); 
					int losses = result.getInt(4);
					int playedGames = wins + losses;
					float winPercentage = ( (float)wins / playedGames ) * 100;
					
					// Put all found username and wins in HashMap
					HashMap<String, String> userInfo = new HashMap<String, String>();
					userInfo.put(NAME, result.getString(1));
					userInfo.put(RANK, result.getString(2));
					userInfo.put(WIN_PERCENTAGE, String.format("%.2f", winPercentage));
					userInfo.put(PLAYED_GAMES, Integer.toString(playedGames));
					
					// put all userInfo in array
					users.add(userInfo);
				}
				
			}
			
		}
		
		catch (SQLException e)
		{
			zoneExtension.trace("SQL Error: " + e.getMessage());
			return users;
		}

		finally
		{
			// Return connection to the DBManager connection pool
			try {
				if ( statement != null ) {
					statement.close();
				}
				if ( connection != null ) {
					connection.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}			
		}
		
		return users;
		
	}
	
	
	public ArrayList<HashMap<String, String>> getFriendsRankingList(ArrayList<String> friendNames)
	{
		
		Connection connection = null;
		ArrayList<HashMap<String, String>> users = new ArrayList<HashMap<String, String>>();
		PreparedStatement statement = null;
		
		// If there is no friends return empty array
		if ( friendNames.size() == 0 ) {
			return users;
		}
		
		zoneExtension.trace("friends: " + friendNames);
		
		try
		{
			// Grab a connection from the DBManager connection pool
			connection = dbManager.getConnection();

			// Build statement
			String statmentString = "SELECT name, rank FROM user WHERE name IN ( ";
			StringBuilder sb = new StringBuilder();
			sb.append(statmentString);
			
			for (int i = 0; i < friendNames.size() - 1; i++) {
				sb.append("?" + " , " );
			}
			
			sb.append("?");
		    sb.append(" ) ORDER BY rank DESC LIMIT 25");
		    statmentString = sb.toString();
		    
			statement = connection.prepareStatement(statmentString);
			
			for (int i = 1; i <= friendNames.size(); i++) {
				statement.setString(i, friendNames.get(i-1) );
			}

			zoneExtension.trace("statement: " + statement);
			
			// Execute query
			ResultSet result = statement.executeQuery();

			// Verify that one record was found
			if ( !result.first() ) {
				
				zoneExtension.trace("There is a problem in getFriendsRankingList()");

			} else {				
				
				// Get number of found usernames
				ResultSetMetaData metadata = result.getMetaData();
				int numberOfColumns = metadata.getColumnCount();
								
				zoneExtension.trace("firstResult 1 : " + result.getString(1)); // name
				zoneExtension.trace("firstResult 2 : " + result.getString(2)); // rank
				zoneExtension.trace("firstResult 3 : " + result.getString(3)); // wins
				zoneExtension.trace("firstResult 4 : " + result.getString(4)); // losses
				
				// move beforeFirst or the first result will be missing
				if ( numberOfColumns > 0 ) {
					result.beforeFirst();
				}
				
				while ( result.next() ) {
					
					// Calculate playedGames and winPercentage
					int wins = result.getInt(3); 
					int losses = result.getInt(4);
					int playedGames = wins + losses;
					float winPercentage = ( (float)wins / playedGames ) * 100;
					
					// Put all found username and wins in HashMap
					HashMap<String, String> userInfo = new HashMap<String, String>();
					userInfo.put(NAME, result.getString(1));
					userInfo.put(RANK, result.getString(2));
					userInfo.put(WIN_PERCENTAGE, String.format("%.2f", winPercentage));
					userInfo.put(PLAYED_GAMES, Integer.toString(playedGames));
					
					// put all userInfo in array
					users.add(userInfo);
				}
				
			}
			
		}
		
		catch (SQLException e)
		{
			zoneExtension.trace("SQL Error: " + e.getMessage() + " errorCode: " + e.getErrorCode() + 
								" localizedMessage: " + e.getLocalizedMessage());
			
			return users;
		}

		finally
		{
			// Return connection to the DBManager connection pool
			try {
				if ( statement != null ) {
					statement.close();
				}
				if ( connection != null ) {
					connection.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}			
		}
		
		return users;
		
	}
	
	
	// INSERT new GameOverInfo to 'game' table
	public void insertGameOverInfo(GameOverInfo gameOverInfo) 
	{

		Connection connection = null;
		PreparedStatement statement = null;
		try
		{
			// Grab a connection from the DBManager connection pool
			connection = dbManager.getConnection();
			
			// Prepare the statement
			statement = connection.prepareStatement("INSERT INTO `game` ( "
					+ "`winner`, `firstPlayerName`, `secondPlayerName`, "
					+ "`firstPlayerHeroType`, `firstPlayerProduces`, `firstPlayerAttack`, "
					+ "`secondPlayerHeroType`, `secondPlayerProduces`, `secondPlayerAttack`, "
					+ "`performedMoves`, `firstPlayerIsBot`, "
					+ "`secondPlayerIsBot`, `timeElapsed`) " 
					+ " VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )");
			
			statement.setObject(1, gameOverInfo.winner);
			statement.setObject(2, gameOverInfo.firstPlayerName);
			statement.setObject(3, gameOverInfo.secondPlayerName);
			statement.setObject(4, gameOverInfo.firstPlayerHeroType);
			statement.setObject(5, gameOverInfo.firstPlayerHeroProduces);
			statement.setObject(6, gameOverInfo.firstPlayerHeroAttack);
			statement.setObject(7, gameOverInfo.secondPlayerHeroType);
			statement.setObject(8, gameOverInfo.secondPlayerHeroProduces);
			statement.setObject(9, gameOverInfo.secondPlayerHeroAttack);
			statement.setObject(10, gameOverInfo.performedMoves);
			statement.setObject(11, gameOverInfo.firstPlayerIsBot);
			statement.setObject(12, gameOverInfo.secondPlayerIsBot);
			statement.setObject(13, gameOverInfo.timeElapsed);

			// Execute Insert
			statement.executeUpdate();

		}

		// User name was not found
		catch (SQLException e)
		{
			zoneExtension.trace("SQL Error: " + e.getMessage());
		}

		finally
		{
			// Return connection to the DBManager connection pool
			try {
				if ( statement != null ) {
					statement.close();
				}
				if ( connection != null ) {
					connection.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}			
		}

	}
	
	public long getAmountOfMoney(String userName) {

		Connection connection = null;
		long amountOfMoney = 0;
		PreparedStatement statement = null;

		try
		{
			// Grab a connection from the DBManager connection pool
			connection = dbManager.getConnection();

			// Build a prepared statement
			statement = connection.prepareStatement("SELECT money FROM user WHERE name=?");
			statement.setString(1, userName);

			// Execute query
			ResultSet result = statement.executeQuery();

			// Verify that one record was found
			if ( !result.first() )
			{
				zoneExtension.trace("There is no record for username: " + userName);
			}

			amountOfMoney = result.getLong("money");

		}

		// User name was not found
		catch (SQLException e)
		{
			zoneExtension.trace("SQL Error: " + e.getMessage());
			return amountOfMoney;
		}

		finally
		{
			// Return connection to the DBManager connection pool

			try {
				if ( statement != null ) {
					statement.close();
				}
				if ( connection != null ) {
					connection.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}			
		}

		return amountOfMoney;

	}
	
	// UPDATE field in 'hero' table for particular userName and heroType
	public void upgradeHero(String userName, int heroType, int upgradeType ) 
	{
		
		String upgradeField;
		int upgradeValue;
		
		if ( upgradeType == UPGRADE_ATTACK_TYPE ) {
			
			upgradeField = "attack";
			upgradeValue = -1;
			
		} else if ( upgradeType == UPGRADE_PRODUCES_TYPE ) {
			
			upgradeField = "produces";
			upgradeValue = 1;
			
		} else {
			// Not processed type, something went wrong...
			zoneExtension.trace("Not processed type, something went wrong in 'upgradeHero()'");
			return;
		}
		
		Connection connection = null;
		PreparedStatement statement = null;
		try
		{
			// Grab a connection from the DBManager connection pool
			connection = dbManager.getConnection();

			// Increase the field value by 1 for particular userName
			String sqlString;
			
			sqlString = "UPDATE user JOIN hero "
						+ "ON user.name = ? "
						+ "AND hero.heroType = ? "
						+ "SET hero." + upgradeField + " = hero." + upgradeField + " + " + upgradeValue ;

			// Prepare the statement
			statement = connection.prepareStatement(sqlString);
			statement.setString(1, userName);
			statement.setObject(2, heroType);

			zoneExtension.trace("UPDATE statement: " + statement.toString());

			// Execute UPDATE
			statement.executeUpdate();

		}

		// There is some SQL Error
		catch (SQLException e)
		{
			zoneExtension.trace("SQL Error: " + e.getMessage());
		}

		finally
		{
			// Return connection to the DBManager connection pool
			try {
				if ( statement != null ) {
					statement.close();
				}
				if ( connection != null ) {
					connection.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}			
		}

	}

}

